﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_BDDB]
	(
	
	@userid VARCHAR(20),
	@username VARCHAR(20),
	@StartDate NCHAR(10),
	@EndDate NCHAR(10)
	)
AS
BEGIN

Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')



	--销售额等于百度开户+百度服务费-退款销售额  续费销售额等于续费+百度服务费续费-续费退款销售额  毛利是一样的 
 --DECLARE @xzcds INT ,@sxs INT,@zsxs int,@xdxse DECIMAL(12,2),@xdml DECIMAL(12,2),@xfxse  DECIMAL(12,2),@xfml DECIMAL(12,2),@xse DECIMAL(12,2),@ml DECIMAL(12,2) 
	--DECLARE @strSql VARCHAR(max),@strSql1 VARCHAR(max)
	
Declare @indextable Table(id int Identity(1,1),cid INT,CompanyName NVARCHAR(200),mid INT,mname NVARCHAR(100),xzcds INT ,sxs INT,zsxs int,xdxse DECIMAL(12,2),xdml DECIMAL(12,2),xfxse  DECIMAL(12,2),xfml DECIMAL(12,2),xse DECIMAL(12,2),ml DECIMAL(12,2))
Insert Into @indextable([xzcds],[sxs],[zsxs],[xdxse],[xdml],[xfxse],[xfml],[xse],[ml],cid,CompanyName,mid,mname) 
	
--;WITH  list as (
SELECT 	
  --新增成单数 优惠返款算在百度里2012-10-15 AND ProductId not IN (22,29,33)
 --SET @xzcds= 
 isnull((SELECT COUNT(DISTINCT OrderId) FROM [Project]  p
         WHERE p.CompanyId=c.Id AND   TypeId=1 AND ProductId IN (1,37,23)  AND NOE_Flag<>2 
         AND CreateDate BETWEEN @sRq AND @eRq  and p.OrderId NOT IN (SELECT oid FROM 
Order_Change oc WHERE oid IN (SELECT distinct orderid FROM Project p2 WHERE 
                       p2.CompanyId=c.Id AND  p2.CreateDate<@sRq and p2.OrderId=p.OrderId )

 AND oc.shqrq BETWEEN  @sRq AND @eRq)  AND p.SalePrice!=0  ),0)
 xzcds, 
  --上线数 AND ProductId not IN (22,29,33)
 --SET @sxs= 
 isnull((SELECT COUNT (DISTINCT OrderId) FROM [Project] p
  WHERE typeid=1 AND p.CompanyId=c.Id AND   FinanceAudit=2 AND ProductId IN (1,37,23)  AND NOE_Flag<>2  AND CreateDate BETWEEN @sRq AND @eRq   and p.OrderId NOT IN (SELECT oid FROM 
Order_Change oc WHERE oid IN (SELECT distinct orderid FROM Project p2 WHERE 
                       p2.CompanyId=c.Id AND  p2.CreateDate<@sRq and p2.OrderId=p.OrderId )

 AND oc.shqrq BETWEEN  @sRq AND @eRq)  AND p.SalePrice!=0),0)
  sxs,
   --总上线数 AND ProductId not IN (22,29,33)
 --SET @zsxs= 
 isnull((SELECT COUNT (DISTINCT OrderId) FROM [Project] p
         WHERE p.CompanyId=c.Id AND   typeid=1 AND FinanceAudit=2  AND NOE_Flag<>2  AND ProductId IN (1,37,23) AND FinanceAuditDate BETWEEN @sRq AND @eRq   and p.OrderId NOT IN (SELECT oid FROM 
Order_Change oc WHERE oid IN (SELECT distinct orderid FROM Project p2 WHERE 
                       p2.CompanyId=c.Id AND  p2.CreateDate<@sRq and p2.OrderId=p.OrderId )

 AND oc.shqrq BETWEEN  @sRq AND @eRq)  AND p.SalePrice!=0),0)
 zsxs, 
  --新单销售额 AND ProductId not IN (22,29,33)
 --SET @xdxse=  
 isnull((SELECT sum(SalePrice) FROM [Project] WHERE [Project].CompanyId=c.Id AND   TypeId=1  AND NOE_Flag<>2 AND ProductId IN (1,37,23)   AND CreateDate BETWEEN @sRq AND @eRq),0)  
 xdxse, 
  --新单毛利 AND ProductId not IN (22,29,33)
 --SET @xdml= 
 isnull((SELECT SUM(SalePrice-CostPrice) FROM [Project] WHERE [Project].CompanyId=c.Id AND   TypeId=1  AND NOE_Flag<>2 AND ProductId IN (1,37,23)  AND CreateDate BETWEEN @sRq AND @eRq),0)   
  xdml,
  --续费销售额 AND ProductId not IN (22,29,33)
 --SET @xfxse= 
 isnull((SELECT sum(SalePrice) FROM [Project] WHERE [Project].CompanyId=c.Id AND   TypeId=2  AND ProductId IN (1,37,23)  AND NOE_Flag<>2  AND CreateDate BETWEEN @sRq AND @eRq),0)  
  xfxse,
  --续费毛利 AND ProductId not IN (22,29,33)
 --SET @xfml= 
 isnull((SELECT (SUM(SalePrice)-sum(CostPrice)) FROM  [Project] WHERE [Project].CompanyId=c.Id AND   TypeId=2  AND NOE_Flag<>2  AND ProductId IN (1,37,23)   AND CreateDate BETWEEN @sRq AND @eRq),0) 
  xfml,


  --销售额合计 AND ProductId not IN (22,29,33)
 --SET  @xse=
  isnull((SELECT sum(SalePrice) FROM [Project] WHERE [Project].CompanyId=c.Id AND ProductId IN (1,37,23)  AND NOE_Flag<>2   AND CreateDate BETWEEN @sRq AND @eRq),0) 
  xse,
  --毛利合计 AND ProductId not IN (22,29,33)
 --SET @ml=
 isnull((SELECT sum(SalePrice-CostPrice) FROM [Project] WHERE [Project].CompanyId=c.Id AND   ProductId IN (1,37,23)    AND NOE_Flag<>2 AND CreateDate BETWEEN @sRq AND @eRq),0)  
  ml,c.id,c.CompanyName,c.mid, (SELECT ma.CompanyName FROM ManagementArea ma WHERE ma.Id=c.mid) AS mname
FROM Company c ORDER BY c.Mid
--WHERE id IN ('+@CompanyId+')




                                                
Declare @start int, @end int,  @today INT ,@a int
	Select  @end=Count(Id) From @indextable 
	Select  @start=1
	While(@start<=@end)
BEGIN
	select @a= cid from @indextable where id=@start
SELECT @today=COUNT(1) FROM bddb WHERE Convert(nchar(10),startdate,120)=@StartDate and Convert(nchar(10),enddate,120)=@EndDate AND companyid=@a
IF 	@today>0
BEGIN
	DELETE bddb WHERE Convert(nchar(10),startdate,120)=@StartDate and Convert(nchar(10),enddate,120)=@EndDate AND companyid=@a
END
--select * from list
INSERT INTO [bddb]([CompanyId],[CompanyName],[Mid]
           ,[MName],[xzcds],[sxs],[zsxs],[xdxse],[xdml],[xfxse],[xfml],[xse],[ml],[userid],[startdate],[enddate],username)
select cid,companyname,mid,mname,[xzcds],[sxs],[zsxs],[xdxse],[xdml],[xfxse],[xfml],[xse],[ml],
@userid,@StartDate,@EndDate,@username from @indextable where cid=@a
Set @start=@start+1--自增
End

 --SELECT @xzcds AS xzcds,@sxs AS sxs,@zsxs AS zsxs,@xdxse AS xdxse,@xdml AS xdml,@xfxse AS xfxse,@xfml AS xfml,@xse AS xse,@ml AS ml

 --EXEC(@strSql+@strSql1)
 --PRINT(@strSql+@strSql1)

END
